How to join 2 queries into 1

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_number 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

Re: How to join 2 queries into 1

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_number 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_number
123 abc123456
124 abd345678

System
systemid system_name
123 somesystemname
124 someothername

select s.system_name,b.bios_serial_number 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

Re: How to join 2 queries into 1

"Shanimal" wrote in message
news:1142009430.974883.320470@i39g2000cwa.googlegroups.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.

Re: How to join 2 queries into 1

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.

Re: How to join 2 queries into 1

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_description
bios_manufacturer
bios_serial_number
bios_sm_bios_version
bios_version
bios_asset_tag
bios_timestamp
bios_first_timestamp

The BIOS doesn't include the system name.

The system table holds the following information (columns):

system_num_processors
system_memory
system_build_number
net_ip_address
system_uuid
net_domain
net_user_name
net_client_site_name
net_domain_controller_address
net_domain_controller_name
system_model
system_name
system_part_of_domain
system_primary_owner_name
system_system_type
system_id_number
system_vendor
time_caption
time_daylight
system_boot_device
system_os_type
system_os_name
system_country_code
system_description
system_organisation
system_language
system_registered_user
system_serial_number
system_service_pack
system_version
system_windows_directory
audit_type
firewall_enabled_domain
firewall_disablenotifications_domain
firewall_donotallowexceptions_domain
net_domain_role
firewall_enabled_standard
firewall_disablenotifications_standard
firewall_donotallowexceptions_standard
virus_manufacturer
virus_version
virus_name
virus_uptodate
date_virus_def date
date_system_install
system_timestamp
system_first_timestamp

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)!

Re: How to join 2 queries into 1

"Shanimal" wrote in message
news:1142017818.889924.23410@i40g2000cwc.googlegroups.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.

Re: How to join 2 queries into 1

Bill Karwin wrote:
> "Shanimal" wrote in message
> news:1142017818.889924.23410@i40g2000cwc.googlegroups.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_number 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

Re: How to join 2 queries into 1

Shanimal wrote:
> Bill Karwin wrote:
>
>>"Shanimal" wrote in message
>>news:1142017818.889924.23410@i40g2000cwc.googlegroups.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_number 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_number 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,system_id_number as bois_serial_number 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.