Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

sql select query joining multiple tables with foreign keys

Posted on 2011-09-25
5
460 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

856 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