473,231 Members | 1,956 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,231 software developers and data experts.

All Records From Table A - All Records From Table B - Join Alike Records

I am by no means a SQl Jedi as will be apparent by my question, but I
can usually figure out a select statement on my own. I have one today
though that really has me stumped. I am working in MySQlL 5.

In My first select statement I get all my records from Table B
SELECT
`table_A`.`ITEM`,
`table_A`.`DECSCRIPTION`,
`table_A`.`UM`,
`table_A`.`PHASE`,
`table_B`.`Qty`,
`table_B`.`Calc` as calculated
FROM
`table_A`
Inner Join `table_B` ON `req_itemlist`.`ITEM` = `table_B`.`ItemID`

In my second statement I get my records that match in this case phase
401 in Table B and all my Table A records for phase 401.

SELECT
`table_A`.`ITEM`,
`table_A`.`DECSCRIPTION`,
`table_A`.`UM`,
`table_A`.`PHASE`,
`table_B`.`Qty`,
`table_B`.`Calc` as calculated
FROM
`table_A`
Left Outer Join `table_B` ON `req_itemlist`.`ITEM` = `table_B`.`ItemID`
Where
table_A.PHASE In ('401' )

Now I need to combine the Data of both recordsets. I need EVERYTHING in
Table B, but I also need All Table A records that match the phase
selection....

Can I write this one query or do I need to use a Temp table?

Oct 3 '06 #1
2 3225
The UNION clause can be used to join the results of two queries.

http://dev.mysql.com/doc/refman/5.1/en/union.html

The basic syntax is..

CREATE TABLE test1 (id int, something varchar(50));
CREATE TABLE test2 (id int, something varchar(50));

INSERT INTO test1 (id, something) VALUES (1, 'hello'),(2, 'world');
INSERT INTO test2 (id, something) VALUES (1, 'foo'),(2, 'bar');

SELECT something, id FROM test1
UNION
SELECT something, id FROM test2;

kevinjbowman wrote:
I am by no means a SQl Jedi as will be apparent by my question, but I
can usually figure out a select statement on my own. I have one today
though that really has me stumped. I am working in MySQlL 5.

In My first select statement I get all my records from Table B
SELECT
`table_A`.`ITEM`,
`table_A`.`DECSCRIPTION`,
`table_A`.`UM`,
`table_A`.`PHASE`,
`table_B`.`Qty`,
`table_B`.`Calc` as calculated
FROM
`table_A`
Inner Join `table_B` ON `req_itemlist`.`ITEM` = `table_B`.`ItemID`

In my second statement I get my records that match in this case phase
401 in Table B and all my Table A records for phase 401.

SELECT
`table_A`.`ITEM`,
`table_A`.`DECSCRIPTION`,
`table_A`.`UM`,
`table_A`.`PHASE`,
`table_B`.`Qty`,
`table_B`.`Calc` as calculated
FROM
`table_A`
Left Outer Join `table_B` ON `req_itemlist`.`ITEM` = `table_B`.`ItemID`
Where
table_A.PHASE In ('401' )

Now I need to combine the Data of both recordsets. I need EVERYTHING in
Table B, but I also need All Table A records that match the phase
selection....

Can I write this one query or do I need to use a Temp table?
Oct 3 '06 #2
kevinjbowman wrote:
I am by no means a SQl Jedi as will be apparent by my question, but I
can usually figure out a select statement on my own. I have one today
though that really has me stumped. I am working in MySQlL 5.

In My first select statement I get all my records from Table B
SELECT
`table_A`.`ITEM`,
`table_A`.`DECSCRIPTION`,
`table_A`.`UM`,
`table_A`.`PHASE`,
`table_B`.`Qty`,
`table_B`.`Calc` as calculated
FROM
`table_A`
Inner Join `table_B` ON `req_itemlist`.`ITEM` = `table_B`.`ItemID`

In my second statement I get my records that match in this case phase
401 in Table B and all my Table A records for phase 401.

SELECT
`table_A`.`ITEM`,
`table_A`.`DECSCRIPTION`,
`table_A`.`UM`,
`table_A`.`PHASE`,
`table_B`.`Qty`,
`table_B`.`Calc` as calculated
FROM
`table_A`
Left Outer Join `table_B` ON `req_itemlist`.`ITEM` = `table_B`.`ItemID`
Where
table_A.PHASE In ('401' )

Now I need to combine the Data of both recordsets. I need EVERYTHING in
Table B, but I also need All Table A records that match the phase
selection....

Can I write this one query or do I need to use a Temp table?

You can use UNION but you have to make sure your field counts match.
Oct 4 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Edward | last post by:
SQL Server 7.0 If I run the following in Query Analyzer I get no records returned: exec GetLeadsOutcome_Dealer '1/1/2003','12/2/2003',10, '176, 183' If, however, I run either : exec...
2
by: AG | last post by:
I have a very big table with 20 million records DistinctProjectionKey which i join several times to different tables in this query. select distinct distinctprojectionkeyid,dpk.MarketID,...
8
by: tom | last post by:
I am new to SQL administration. >From a list of IDs that are the primary key in one table (i.e. Customer Table), I want to make changes in tables that use those IDs as a foreign key. ...
6
by: Sven Pran | last post by:
Probably the answer is there just in front of me only awaiting me to discover it, but: 1: I want to build a query that returns all records in one table for which there is no successful "join"...
15
by: Hi5 | last post by:
Hi, I am designing a database for a client in which It has a client table including the followings: 1-Table Client 2-Table lookupcategory 3-Table Ctegory
12
by: jaYPee | last post by:
I have currently using a dataset to access my data from sql server 2000. The dataset contains 3 tables that is related to each other. parent/child/grandchild relationship. My problem is it's very...
3
by: Cindy | last post by:
I'm trying to use the NEWID function in dynamic SQL and get an error message Incorrect syntax near the keyword 'ORDER'. Looks like I can't do an insert with an Order by clause. Here's the code:...
1
by: babamc4 | last post by:
Hi All, I am trying to run a query to include null fields. My tables are as follows: Table Demographics 54 records (Main table) Table Follow Up During Radiotherapy 100+ records (Sub table)...
1
by: bluereign | last post by:
Thank you for your assistance. I am a novice looking to JOIN and append or combine records from 2 current Tables into 2 new Tables named below. I have been able to JOIN Tables with the script...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.