how to fetch records from multiple tables

Question

how to fetch records from multiple tables

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

CELKO (12/28/2012)
{ISO-11179 rules for data element names} Which can be downloaded where?
http://metadata-standards.org/Document-library/Documents-by-number/WG2-N1551-N1600/WG2N1580_WD_11179-5_Ed3.pdf
But you will sorry. Reading standards is like reading the law; it took me two years on ANSI X3H2 to get comfortable with the language. I would start with some of my stairway stuff on the basics:

And yet you continue to bash people about learning a standard that you've just said they'd be sorry for reading. Stop pushing that garbage. Teach them the right way to do it instead. If you believe that your "stairway" article is more effective, then start posting that instead of bothering people with useless rhetoric about a standard that even took you a long time to understand.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

manibad (12/27/2012)
i have Demo,elections and electionshist tables where i should neglect the pepole who have status='t' from demo table and for those who are not neglected i should check whether electionstartdate is null and electionterminationdate is not null from election table then for these people i should check in electionhist table that whether the person has electionstartdate is not null(the value must be fetched from the top most load.
In demo and election tables we have nearly 50000 -60000 records but in electionhist tables nearly 10000000 records are there.
Can anyone please suggest me in pulling records when a situation prevails like this....i am able to do it but it takes a longer time....i need to pull it in mins.
I have given you the entire picture of the situation..Pleas go through this and let me know your suggestions.Thanks in advance
{snip}
If you are not clear with this please let me know i will explain you more.

Although people can certainly make recommendations to you about the code you've posted, they probably won't be able to help you really come to the right conclusions because the don't have your data and you've not provided the right kind of information.

Please see the article at the second link in my signature line below for how to post the correct information to get a performance problem of this nature solved.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101098 More actions · Answer 3

Jeff Moden (12/28/2012)
Sean Lange (12/28/2012)
First of all, SSN have and will be reused. When people die their SSN gets recycled into the available pool.
While it is true that a person can change their SSN, it's a myth that SSNs are recycled. Please see Q20 on the following webpage which is on the official Social Security website.
http://www.socialsecurity.gov/history/hfaq.html
There was one incident many years ago where some secretary had her SS card used as a demo card in wallets and some huge number of people claimed it as their own, but Social Security numbers have not been reused to date.

That seems to imply that someone can change his/her SSN at will. I don't believe that's true.

But a SSN can change. I would think the most common reason is a non-citizen with a "SSN" as a tax number becoming a citizen. He/She must be assigned a new SSN, as non-citizen numbers come from a different pool (or least they used to). A leading "000", for example, was reserved for non-citizens.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

ScottPletcher SSC Guru Points: 101098 More actions · Answer 4

CELKO (12/31/2012)
That seems to imply that someone can change his/her SSN at will. I don't believe that's true.
Yep, you can. And thanks to identity theft this is becoming more common to do this.
I had a friend who worked in the Nordic countries years ago. His government ID number include his birth date in it and code for the place of birth. But they screwed up and gave him Feb 30 or some other impossible date. All of the car rentals, etc rejected him!

NO, you CAN'T. The SSN administration has to approve it. You have to have a valid reason to do it, you can't just arbitrarily ("at will") decide to change your number.

Do you have ANY link to show that you can change your SSN at will, any time you want??

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

a4apple SSCrazy Points: 2620 More actions · Answer 5

Select *

from demo d

INNER JOIN elections e ON e.sno = d.sno

Inner join electionshist esh ON esh.ssno = e.ssno and esh.sno = d.sno

where d.status <> 't'

and e.electionstartdate is null

and e.electionstermdate is not null

Is this what you are looking for???

Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 6

A few words regarding SSNs:

1) It's identifying information. Damn near illegal to physcially store next to medical data on a client. You will fail audits if your encryption isn't near perfect... which makes it pretty damned useless as a PK. GUID or Identity allow for confidentiality.

2) Ever worked in manufacturing? I had five 'legals' who all stupidly got their identities from the same fake paperwork moron. You'd think he'd rotate numbers or SOMETHING. No. Now, ignoring the obvious legal ramifications of this problem, let's just work with the fact that an emergency room CANNOT refuse to accept a patient. They can deport them later, but they must be allowed to be treated NOW, particularly if they're bleeding on the floor. Using a user controlled business key in a situation like that just leads to problems.

Joe, you're on a crusade against identities. Fine. Arbitrary internal keys that have no bearing on ANY actual external data are exactly what is required in some cases. They're also damned easy to use. What do I want to do, go have some poor shmuck on the floor looking up DUNS numbers all day long for every new client we bring in, or slap a number on 'em and go from there and when someone who cares has time when we actually need that information for the 2% of the clients who become real values to the company can go get it as an attribute?

Who cares if order numbers are an arbitrary insert count # or if they're procedurally generated according to some encoding. It's an order number. It's simply some value to be looked up. Who cares how it's generated as long as it doesn't repeat? Well, hunh! Identity doesn't repeat unless you screw up the system manually.

You constantly bash people for using simple tools to simple problems. There is no reason to create systems to go hunt down DUNS numbers (which requires reliance on Dun & Bradstreet, an external company with fees) and many small businesses don't require (neither of mine have one, for example, and they do business just fine). Particularly since most companies literally do not care what that value is. You've added overhead, frustration, and exceptions to a system that could have been dealt with by simply using an autoincrementing surrogate key.

Please. I'm all for including smart business keys as alternate keys, but get off the horse already.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

ScottPletcher (12/31/2012)
That seems to imply that someone can change his/her SSN at will. I don't believe that's true.

Apologies... I certainly didn't mean to imply such a thing. There has to be a good reason or the SSA won't do it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sean Lange SSC Guru Points: 286590 More actions · Answer 8

Jeff Moden (12/28/2012)
Sean Lange (12/28/2012)
First of all, SSN have and will be reused. When people die their SSN gets recycled into the available pool.
While it is true that a person can change their SSN, it's a myth that SSNs are recycled. Please see Q20 on the following webpage which is on the official Social Security website.
http://www.socialsecurity.gov/history/hfaq.html
There was one incident many years ago where some secretary had her SS card used as a demo card in wallets and some huge number of people claimed it as their own, but Social Security numbers have not been reused to date.

Thanks for the correction Jeff.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/