Solved

sql select query joining multiple tables with foreign keys

Posted on 2011-09-25
5
461 Views
Last Modified: 2012-05-12
Hi There!

Hoping if someone would be able to help me out?

I am trying to write a select query that joins all tables I have got the following code:
SELECT 
			DATE_FORMAT(OrdersDate, '%M %d, %Y'), 
			LocalOrderNumberValue, 
			OrdersName, 
			OrdersQuantity, 
			InsertSizeValue, 
			InsertStockValue, 
			CoverStockFinishValue, 
			CoverFormatSizeValue, 
			Content, 
			StrategicApplicationValue, 
			DistributionChannelValue, 
			DistributionChannelDetailsValue, 
			AccountValue, 
			Country, 
			IndustrySectorValue, 
			IndustrySubSectorValue
		FROM 
			account,
			content
			country,
			cover_format_size,
			cover_stock_finish,
			distribution_channel,
			distribution_channel_details,
			industry_sector,
			industry_sub_sector,
			insert_size,
			insert_stock,
			local_order_number,
			orders,
			orders_bridge_account,
			strategic_application
		WHERE 
			orders.AccountID = orders_bridge_account.AccountID
			AND orders_bridge_account.OrdersID = orders.OrdersID
			AND orders_bridge_account.AccountID = account.AccountID
			AND orders.ContentID = content.ContentID
			AND orders.CountryID = country.CountryID
			AND orders.CoverFormatSizeID = cover_format_size.CoverFormatSizeID
			AND orders.CoverStockFinishID = cover_stock_finish.CoverStockFinishID
			AND orders.DistributionChannelID = distribution_channel.DistributionChannelID
			AND orders.DistributionChannelDetailsID = distribution_channel_details.DistributionChannelDetailsID
			AND orders.IndustrySectorID = industry_sector.IndustrySectorID
			AND orders.IndustrySubSectorID = industry_sub_sector.IndustrySubSectorID
			AND orders.InsertSizeID = insert_size.InsertSizeID
			AND orders.InsertStockID = insert_stock.InsertStockID
			AND orders.LocalOrderNumberID = local_order_number.LocalOrderNumberID
			AND orders.StrategicApplicationID = strategic_application.StrategicApplicationID
		ORDER BY 
			$order_by 
		LIMIT 
			$start, $display";

Open in new window


I would however like to add to that code a variable that would allow for the results to be refinded. eg. If the user selects country to be equal to South Africa...

Also another example ... if the user would like the date to be between $date1 and $date2 and for the country to be equal to South Africa...

What should I add to my sql query? Would I be able to just add lines of code to the WHERE part... for example:
SELECT
...
FROM
...
WHERE 
			orders.AccountID = orders_bridge_account.AccountID
			AND orders_bridge_account.OrdersID = orders.OrdersID
			AND orders_bridge_account.AccountID = account.AccountID
AND country = $country
AND date = $date
...

Open in new window


What way would be best??

Also just hoping for a little insight into indexes - what index should I make for a statement like this? I'm new to SQL and databases and would be great if I could get a little help !

Thanks so much in advance!!
Chelsesa
0
Comment
Question by:h3rm1t9536
  • 3
5 Comments
 
LVL 15

Expert Comment

by:Eyal
ID: 36594928
SELECT
...
FROM
...
WHERE 
			orders.AccountID = orders_bridge_account.AccountID
			AND orders_bridge_account.OrdersID = orders.OrdersID
			AND orders_bridge_account.AccountID = account.AccountID
AND (country = $country OR $country is null)
AND (date = $date or $date is null)
...

Open in new window

0
 
LVL 2

Expert Comment

by:Lord_Death
ID: 36594940
declare @WorkGroupID decimal = 0;
SELECT   *
From  ....
WHERE   (@WorkGroupID = 0 OR OwnerWorkGroupID = @WorkGroupID) AND ...
0
 

Author Comment

by:h3rm1t9536
ID: 36594994
Hi Eval

Sorry what does it mean when you add '...OR $country is null' to the query? Does it mean select all rows where country is equal to South Africa and where country is equal to NULL?

Sorry and a quick question when I fetch the records and try print them would the code be something like this....
$row['insert_stock.InsertStockValue']

or would it be like this
$row['InsertStockValue']?

Thanks for your help!
0
 
LVL 15

Accepted Solution

by:
Eyal earned 250 total points
ID: 36594998
if you pass the parameter $country as NULL so the sql will see it like so... NULL is NULL
that means true and all rows will be returned
if the $country will not be null then this condision will be ignored and country = $country will be applied
0
 
LVL 15

Expert Comment

by:Eyal
ID: 36594999
and regarding the print $row['InsertStockValue'] will do
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
show child records separated by commas 12 38
activeMQ Queue Messages Enqueued and Messages Dequeued 1 39
SQL- GROUP BY 4 25
Need to trim my database size 9 26
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question