How to join 2 queries into 1

[Shanimal]

I would like to know how to join 2 queries so that the results of these
2 queries show up in the same query:

SELECT b.bios_serial_n umber FROM bios b:
SELECT s.system_name FROM system s;

Basically I want to create a report that includes both system name and
serial number. I'm new to this and none of the JOIn documentation was
clear to me.

thanks!
PS

[noone]

Shanimal wrote:

I would like to know how to join 2 queries so that the results of these
2 queries show up in the same query: SELECT b.bios_serial_n umber FROM bios b:
SELECT s.system_name FROM system s; Basically I want to create a report that includes both system name and
serial number. I'm new to this and none of the JOIn documentation was
clear to me. thanks!
PS

We would need to see what columns are in each table so that we can see the
relationship between the 2 tables.

Example:
bios
systemid bios_serial_num ber
123 abc123456
124 abd345678

System
systemid system_name
123 somesystemname
124 someothername

select s.system_name,b .bios_serial_nu mber from bios b, system s
where b.???? = s.????;

s.???? and b.???? should contain a the same information pertaining to
system - like a systemID in the above example

[Bill Karwin]

"Shanimal" <sh******@att.n et> wrote in message
news:11******** **************@ i39g2000cwa.goo glegroups.com.. .

Basically I want to create a report that includes both system name and
serial number. I'm new to this and none of the JOIn documentation was
clear to me.

It's a lot of work, and probably not the best use of your time or ours, to
cover the basics on a newsgroup, when so many other materials already exist
for this purpose.

Find another document or tutorial on joins. Keep reading until you
understand joins. Joins are a fundamental concept in SQL, as fundamental as
loops are in most other programming languages. You must understand joins,
or else you might as well be using a spreadsheet.

Try a Google search for "sql join tutorial" and read a few of the free
tutorials available. Or pick up an introductory book like "SQL for Dummies"
(I'm not trying to be condescending, it's really a good book).

Regards,
Bill K.

[noone]

Bill Karwin wrote:

[snip] Or pick up an introductory book like "SQL for Dummies"
(I'm not trying to be condescending, it's really a good book).
I concur.
Regards,
Bill K.

[Shanimal]

noone-

Thanks for trying to help!

I don't think there is a relation between the two tables. The BIOS
table holds the following info (columns):

bios_id
bios_uuid
bios_descriptio n
bios_manufactur er
bios_serial_num ber
bios_sm_bios_ve rsion
bios_version
bios_asset_tag
bios_timestamp
bios_first_time stamp

The BIOS doesn't include the system name.

The system table holds the following information (columns):

system_num_proc essors
system_memory
system_build_nu mber
net_ip_address
system_uuid
net_domain
net_user_name
net_client_site _name
net_domain_cont roller_address
net_domain_cont roller_name
system_model
system_name
system_part_of_ domain
system_primary_ owner_name
system_system_t ype
system_id_numbe r
system_vendor
time_caption
time_daylight
system_boot_dev ice
system_os_type
system_os_name
system_country_ code
system_descript ion
system_organisa tion
system_language
system_register ed_user
system_serial_n umber
system_service_ pack
system_version
system_windows_ directory
audit_type
firewall_enable d_domain
firewall_disabl enotifications_ domain
firewall_donota llowexceptions_ domain
net_domain_role
firewall_enable d_standard
firewall_disabl enotifications_ standard
firewall_donota llowexceptions_ standard
virus_manufactu rer
virus_version
virus_name
virus_uptodate
date_virus_def date
date_system_ins tall
system_timestam p
system_first_ti mestamp

The system table doesn't include any bios serial number.

I want the query to return the system name and bios serial number. So
far I can get one of the other, but not both together. What good is
getting a list of serial numbers for computers when you don't have a
computername?

I've read a couple of tutorials, I've searched the My SQL website and
google, the answer isn't clear. This is why I'm posting here, it's a
legit request. I'm not going to sign up for a class on this when the
newsgroup is readily avaialable and there are people out there willing
to help (there are)!

[Bill Karwin]

"Shanimal" <sh******@att.n et> wrote in message
news:11******** *************@i 40g2000cwc.goog legroups.com...

I don't think there is a relation between the two tables.

You said you want both the system name and the bios serial number. Which
bios serial number goes with which system name?

This is the concept of a join: to express the criteria for matching rows of
one table to rows of another table. How they match up is the relationship
between the two tables.

It's possible that you do not have the right information in these two tables
to express the relationship. This would naturally make it hard to envision
the join criteria.

For instance, as in noone's example, it is typical that there is a column
that appears in both tables. Where you find identical values in that column
in both tables, that's the definition of rows that match. But you don't
seem to have that column in common between the two tables, so you may have
no expressable relationship given the current state of the data.

The other possibility is that you need a third table to express the
relationship. This is useful if one bios can match multiple systems, and
also one system can match multiple bios's. This is called a "many-to-many"
relationship. You'd need to join this third table to both the bios table
and the system table in one query, in order to match up all the entries.

Regards,
Bill K.

[Shanimal]

Bill Karwin wrote:

"Shanimal" <sh******@att.n et> wrote in message
news:11******** *************@i 40g2000cwc.goog legroups.com...

I don't think there is a relation between the two tables.

You said you want both the system name and the bios serial number. Which
bios serial number goes with which system name?

This is the concept of a join: to express the criteria for matching rows of
one table to rows of another table. How they match up is the relationship
between the two tables.

It's possible that you do not have the right information in these two tables
to express the relationship. This would naturally make it hard to envision
the join criteria.

For instance, as in noone's example, it is typical that there is a column
that appears in both tables. Where you find identical values in that column
in both tables, that's the definition of rows that match. But you don't
seem to have that column in common between the two tables, so you may have
no expressable relationship given the current state of the data.

The other possibility is that you need a third table to express the
relationship. This is useful if one bios can match multiple systems, and
also one system can match multiple bios's. This is called a "many-to-many"
relationship. You'd need to join this third table to both the bios table
and the system table in one query, in order to match up all the entries.

Regards,
Bill K.

Bill-

Thanks for taking the time to explain this to me. It totally makes
sense now, after reading just the first paragraph you posted. I was
missing the concept now I get it. As you mentioned, there has to be
some relationship connecting them. Within the system table I found that
system_id_numbe r is the same as the bios serial number so I can figure
out the join concept and get the info that I need.

thanks again!
PS

[noone]

Shanimal wrote:

Bill Karwin wrote:

"Shanimal" <sh******@att.n et> wrote in message
news:11****** *************** @i40g2000cwc.go oglegroups.com. ..

I don't think there is a relation between the two tables.

You said you want both the system name and the bios serial number. Which
bios serial number goes with which system name?

This is the concept of a join: to express the criteria for matching rows of
one table to rows of another table. How they match up is the relationship
between the two tables.

It's possible that you do not have the right information in these two tables
to express the relationship. This would naturally make it hard to envision
the join criteria.

For instance, as in noone's example, it is typical that there is a column
that appears in both tables. Where you find identical values in that column
in both tables, that's the definition of rows that match. But you don't
seem to have that column in common between the two tables, so you may have
no expressable relationship given the current state of the data.

The other possibility is that you need a third table to express the
relationshi p. This is useful if one bios can match multiple systems, and
also one system can match multiple bios's. This is called a "many-to-many"
relationshi p. You'd need to join this third table to both the bios table
and the system table in one query, in order to match up all the entries.

Regards,
Bill K.

Bill-

Thanks for taking the time to explain this to me. It totally makes
sense now, after reading just the first paragraph you posted. I was
missing the concept now I get it. As you mentioned, there has to be
some relationship connecting them. Within the system table I found that
system_id_numbe r is the same as the bios serial number so I can figure
out the join concept and get the info that I need.

thanks again!
PS

Based on this statement:
"Within the system table I found that system_id_numbe r is the same as
the bios serial number so I can figure out the join concept and get the
info that I need."

now you can just

select syste_name,syst em_id_number as bois_serial_num ber from system;

This query "renames" the column name at select time to be whatever you
want it to be...

without having to do a join at all... the purpose of a join is retrieve
non-repeating data from the 2(or more) tables.